const mssql = require("mssql");
const dayjs = require("dayjs");
const scripts = require('./scripts/mssqlMetadata');
const SqlBuilder = require("./SqlBuilder");
const CreateSqlBuilder = require('./CreateSqlBuilder');

class MSSQLHelper {
  constructor(dbIndex) {
    this.type = 'mssql';
    this.dbIndex = dbIndex;
  }

  /**
   * 执行无返回结果集的查询(添加、更新、删除)。
   * @param {String} sql sql语句
   * @param {any} values 参数值
   * @param {Number} dbIndex 数据库标识
   * @returns {Promise} 异步操作对象
   */
  execute (sql, values) {
    return query(sql, values, this.dbIndex);
  }

  /**
   * 添加数据
   * @param {String} tableName 表名称
   * @param {any} datas 数据信息
   * @param {Number} dbIndex 数据库标识
   * @returns {Promise} 异步操作对象
   */
  adds (tableName, datas) {
    let builder = new CreateSqlBuilder(
      tableName,
      Array.isArray(datas) ? datas[0] : datas
    );

    let sql = builder.build();

    if (Array.isArray(datas)) {
      let all = datas.map(d => this.query(sql, builder.values(d), this.dbIndex));

      return Promise.all(all);
    } else {
      return this.query(sql, datas, this.dbIndex);
    }
  }

  /**
   * 查询单条数据。
   * @param {String} sql 查询语句
   * @param {Array} values 参数值
   * @param {Function | Number} callback 动态参数设置回调函数或数据库服务器标识(number型)
   * @param {Number} dbIndex 数据库标识
   * @returns {Promise} 异步操作对象
   */
  async queryForObject (sql, values, callback, database = '') {
    let tempSql = sql;
    let builder = new SqlBuilder(values, 'mssql');

    if (callback != null && typeof callback == "function") {
      callback(builder);
    }

    tempSql += builder.build();

    if (database) {
      tempSql = tempSql.replace(/#dbName/ig, `[${database}]`);
    }

    let rs = {
      at: dayjs().format("YYYY-MM-DD HH:mm:ss"),
      success: false
    };

    let connect = await getConnection(this.dbIndex);

    try {
      let dataRs = await executeSQL(connect, tempSql, values);

      rs.success = true;
      rs.data = dataRs.recordsets && dataRs.recordsets.length > 1 ? dataRs.recordsets : dataRs.recordset[0];

    } catch (err) {
      rs.message = err;
    } finally {
      await connect.close();
    }

    return rs;
  }

  /**
   * 查询多条数据。
   * @param {String} sql 查询语句
   * @param {Array} values 参数值
   * @param {Function | Number | String} callback 动态参数设置回调函数或数据库服务器标识(number型)
   * @param {Number} dbIndex 数据库标识
   * @returns {Promise} 异步操作对象
   */
  async queryForList (sql, values, callback = null, database = '') {
    let tempSql = sql;
    let builder = new SqlBuilder(values, 'mssql', sql.toLowerCase().indexOf(' order by ') != -1);

    if (callback != null && typeof callback == "function") {
      callback(builder);
    }

    tempSql += builder.build();

    if (database) {
      tempSql = tempSql.replace(/#dbName/ig, `[${database}]`);
    }

    let rs = {
      at: dayjs().format("YYYY-MM-DD HH:mm:ss"),
      success: false
    };

    let connect = await getConnection(this.dbIndex, database);

    try {
      let datasP = await executeSQL(connect, tempSql, values);

      rs.success = true;
      rs.datas = datasP.recordsets && datasP.recordsets.length > 1 ? datasP.recordsets : datasP.recordset;

      if (builder.enablePaged) {
        let totalP = await executeSQL(connect, builder.getTotalSql(tempSql), values);

        rs.total = (totalP.recordset || [{ totals: -1 }])[0].totals;
      } else {
        rs.total = datasP.recordset.length;
      }
    } catch (exp) {
      rs.message = exp;
    } finally {
      await connect.close();
    }

    return rs;
  }

  async databases () {
    let rs = await this.queryForList(scripts.DataBases);

    return rs;
  }

  /**
   * 获取数据库的表集合。
   * @param {String} dbName 数据库名称
   * @param {Number} dbIndex 数据库服务器标识
   * @returns {Promise} 异步对象
   */
  async tables (dbName) {
    return await this.queryForList(scripts.Tables, null, null, dbName);
  }

  /**
   * 获取数据库的索引集合。
   * @param {String} dbName 数据库名称
   * @param {Number} dbIndex 数据库服务器标识
   * @returns {Promise} 异步对象
   */
  async indexs (dbName) {
    return await this.queryForList(scripts.TablesIndexs, { dbName });
  }

  /**
   * 获取表的定义信息。
   * @param {String} dbName 数据库名称
   * @param {String} tableName 表名称
   * @param {Number} dbIndex 数据库服务器标识
   * @returns {Promise} 异步对象
   */
  async tableDDL (dbName, tableName) {
    let items = tableName.split('.');

    return await this.queryForObject(scripts.TableDDL, {
      dbName,
      tableName: (items[1] || tableName).replace('[', '').replace(']', '')
    }, null, dbName);
  }

  /**
   * 获取和表相关的操作语句
   * @param {String} dbName 
   * @param {String} tableName 
   */
  async tableDML (dbName, tableName) {
    let rs = await this.columns(dbName, tableName);

    let dmls = [];

    let querySql = '';
    let orders = '';
    let insertColumns = '';
    let insertValues = '';
    let updateColumns = '';
    let whereColumns = '';

    rs.datas.forEach(column => {
      let seperator = column != rs.datas[rs.datas.length - 1] ? ',\n  ' : '';

      querySql += `[${column.column_name}]${seperator}`;

      if (!column.IsIdentity) {
        insertColumns += `[${column.column_name}]${seperator}`;
        insertValues += `<${column.column_name}:${column.column_type}>${seperator}`;
      }

      if (!column.IsIdentity && !column.column_key) {
        updateColumns += `[${column.column_name}]=<${column.column_name}:${column.column_type}>${seperator}`;
      }

      if (column.column_key) {
        orders += (orders.length > 0 ? ',   ' : '');
        orders += `[${column.column_name}]`;

        whereColumns += (whereColumns.length > 0 ? ' AND' : '');
        whereColumns += `[${column.column_name}]=<${column.column_name}:${column.column_type}>`;
      }
    });

    dmls.push('--查询语句');
    dmls.push(`SELECT\n  ${querySql} \nFROM ${tableName}\nWHERE ${whereColumns};`);

    dmls.push('--分页查询语句');
    dmls.push(`SELECT\n  ${querySql} \nFROM ${tableName}\nORDER BY ${orders}\nOFFSET 0 ROWS\nFETCH NEXT 20 ROWS ONLY;`);

    dmls.push('--添加数据');
    dmls.push(`INSERT INTO ${tableName}\n(\n  ${insertColumns}\n)\nVALUES\n(\n  ${insertValues}\n);`);

    dmls.push('--更新数据');
    dmls.push(`UPDATE ${tableName}\nSET ${updateColumns}\nWHERE ${whereColumns};`);

    dmls.push('--删除数据');
    dmls.push(`DELETE FROM ${tableName} WHERE ${whereColumns};`);

    return {
      at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
      success: true,
      datas: dmls
    };
  }

  async status () {
    let rs = await this.queryForList(scripts.ShowStatus, null);

    return rs;
  }

  async monitor () {
    let rs = await this.queryForList(scripts.Monitor, null);

    return rs;
  }

  async processes () {
    let rs = await this.queryForList(scripts.ShowProcessList, null);

    (rs.datas || []).forEach(item => {
      item.CanKill = item.CanKill > 0;
    });

    return rs;
  }

  async killProcess (processId) {
    if (!processId) {
      return {
        at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
        success: false,
        message: '未指定任务编号！'
      };
    }

    let query = "";
    let ids = (processId + '').split(",");

    ids.forEach(v => (query += `kill ${v};`));

    let rs = await this.execute(query, null);

    rs.data = '终止成功！';

    return rs;
  }

  /**
   * 获取表的列信息。
   * @param {String} dbName 数据库
   * @param {String} tableName 表名称
   * @param {Number} dbIndex 数据库服务器标识
   * @returns {Promise} 异步对象
   */
  async columns (dbName, tableName) {
    let items = tableName.split('.');

    return await this.queryForList(
      scripts.TableColumns,
      {
        dbName,
        schema: items[0].replace('[', '').replace(']', ''),
        tableName: items[1].replace('[', '').replace(']', '')
      },
      null,
      dbName
    );
  }

  /**
   * 分页获取表数据。
   * @param {String} dbName 数据库名称
   * @param {String} tableName 表名称
   * @param {Object} so 查询条件
   * @param {Number} dbIndex 数据库服务器标识
   * @returns {Promise} 异步对象。
   */
  async tableDatas (dbName, tableName, so) {
    so = so || {};
    so.dbName = dbName;
    so.tableName = tableName;
    so.pageIndex = so.pageIndex || 1;
    so.pageSize = so.pageSize || 20;

    let rs = {
      at: dayjs().format("YYYY-MM-DD HH:mm:ss"),
      success: false,
      total: 0,
      page: 1,
      columns: [],
      datas: []
    };

    try {
      let builder = new SqlBuilder(so, 'mssql');
      let connection = await getConnection(this.dbIndex, dbName);
      let sql = scripts.TableDatas.replace(/#dbName/ig, `[${dbName}]`).replace(/#tableName/ig, tableName) + builder.build();

      let dataRs = await executeSQL(connection, sql, builder.getValues());
      let totalRs = await executeSQL(connection, builder.getTotalSql(sql), builder.getValues());
      let columnRs = await this.columns(dbName, tableName);

      rs.success = true;
      rs.datas = dataRs.recordset;
      rs.columns = columnRs.datas.map(c => {
        return {
          field: c.column_name,
          title: c.column_comment || c.column_name,
          length: c.character_length
        };
      });
      rs.total = totalRs.recordset[0].totals;

      let limit = so.pageSize || 20;
      rs.page = rs.total % limit == 0 ? rs.total / limit : parseInt(rs.total / limit) + 1;
    } catch (err) {
      rs.message = err.message;
    }

    return rs;
  };
}

const { getDbConfig } = require('../index');

const poolSet = new Map();
const defaultKeys = ["GETDATE", "GETDATE", "GETDATE", "GETDATE", "GETDATE"];
const HasLengthColumnTypes = ['char', 'nchar', 'varchar', 'nvarchar'];

/**
 * 获取数据库连接池。
 * @param {Number} dbIndex 数据库服务器标识
 * @returns {Pool} 数据库连接池
 */
async function getConnection (dbIndex = 0, dbName = '') {
  let setting = getDbConfig(dbIndex);

  if (!poolSet.has(setting.name)) {
    let config = {
      server: setting.Server,
      user: setting.Account,
      password: setting.Password,
      database: dbName || setting.Database,
      options: {
        useUTC: true,
        enableArithAbort: true,
        trustServerCertificate: true
      },
      parseJson: true,
      language: 'zh',
      pool: {
        max: 100,
        min: 5,
        idleTimeoutMillis: 3000
      }
    };

    let pool = new mssql.ConnectionPool(config);

    poolSet.set(setting.name, pool);
  }

  let pool = poolSet.get(setting.name);

  return await pool.connect();
}

/**
 * 执行SQL查询。
 * @param {ConnectionPool} pool 数据库连接对象
 * @param {String} sql SQL语句
 * @param {Array} values 参数数组
 * @returns {Promise} 异步操作对象
 */
let executeSQL = async function (connect, sql, values) {
  let request = await connect.request();

  if (values && typeof values == 'object') {
    let properties = Object.getOwnPropertyNames(values);
    let replacedProps = [];

    sql = sql.replace(/\#(\w+)/ig, function (txt, key) {
      let propName = properties.find(p => p.toLowerCase() == key.toLowerCase());

      if (propName) {
        let rs = values[propName];

        replacedProps.push(propName);

        return rs || '';
      }

      return txt || '';
    });

    replacedProps.forEach(p => {
      delete values[p];
    });

    sql = sql.replace(/\@(\w+)/ig, function (txt, key) {
      let propName = properties.find(p => p.toLowerCase() == key.toLowerCase());

      if (propName) {
        let value = values[propName];

        if (Array.isArray(value)) {
          return `'${value.join("','")}'`;
        } else {
          request.input(key, value);
        }
      }

      return txt;
    });
  }

  return await request.query(sql);
};

/**
 * 数据库处理封装。
 * @param {String} sql SQL语句
 * @param {any} values 参数
 * @param {Function | Number} callback 动态参数设置回调函数或数据库服务器标识(number型)
 * @param {Number} dbIndex 数据库服务器标识
 * @returns {Promise} 异步操作对象
 */
let query = async function (sql, values, callback, dbIndex = 0) {
  let tempSql = sql;
  let builder = new SqlBuilder(values, 'mssql');

  if (typeof callback != "function") {
    dbIndex = callback || 0;
  } else if (callback != null) {
    callback(builder);
  }

  tempSql += builder.build();

  let rs = {
    at: dayjs().format("YYYY-MM-DD HH:mm:ss"),
    success: false
  };

  let connect = await getConnection(dbIndex);

  rs.datas = await executeSQL(connect, tempSql, builder.getValues());
  rs.success = true;

  await connect.close();
};

/**
 * 获取列的默认值。
 * @param value 默认值
 * @returns 默认值的显示
 */
function GetColumnDefaultValue (value) {
  if (defaultKeys.find(c => c.toUpperCase() == value.toUpperCase())) {
    return value;
  }

  return `'${value}'`;
}

MSSQLHelper.prototype.columnChangesDdls = (changeds) => {
  let ddls = [];

  (changeds || []).forEach(item => {
    let table = item.table;

    item.changeds.forEach(column => {
      let sql = "";

      if (column.flag == "add") {
        sql = `ALTER TABLE ${table} ADD[${column.column_name}]`;
        sql += ` ${column.column_type}`;

        if (HasLengthColumnTypes.includes(column.column_type.toLowerCase())) {
          sql += `(${column.character_length == -1 ? 'MAX' : column.character_length})`
        }

        if (column.is_nullable == 1) {
          sql += " NULL";
        } else {
          sql += " NOT NULL";
        }

        if (column.column_default != null) {
          sql += ` DEFAULT ${GetColumnDefaultValue(column.column_default)
            } `;
        }

        sql += `; `;
      } else {
        if (column.column_type != column.target.column_type || column.character_length != column.target.character_length) {
          sql = `ALTER TABLE ${table} ALTER COLUMN[${column.column_name}]`;
          sql += ` ${column.column_type} `;

          if (HasLengthColumnTypes.includes(column.column_type.toLowerCase())) {
            sql += `(${column.character_length == -1 ? 'MAX' : column.character_length})`
          }

          sql += ` ${column.is_nullable == 1 ? 'NULL' : 'NOT NULL'}; \n`;
        }

        if (column.column_default != null) {
          if (column.target.column_default != null) {
            sql += `ALTER TABLE ${table} DROP CONSTRAINT DFC_${column.column_name}; `;
          }

          sql += `ALTER TABLE ${table} ADD CONSTRAINT DFC_${column.column_name} DEFAULT ${GetColumnDefaultValue(column.column_default)} FOR ${column.column_name} `;
        } else if (column.target.column_default != null) {
          sql += `ALTER TABLE ${table} DROP CONSTRAINT DFC_${column.column_name}; `;
        }

        ddls.push(sql);
      }

      if (column.flag == 'add' || (column.target || { column_comment: null }).column_comment != null) {
        ddls.push(`EXEC sys.sp_addextendedproperty 'MS_Description', N'${column.column_comment}', 'SCHEMA', 'dbo', 'TABLE', '${column.table}', 'COLUMN', '${column.column_name}'; \nGO\n`);
      } else if (column.column_comment != column.target.column_comment) {
        ddls.push(`EXEC sys.sp_updateextendedproperty 'MS_Description', N'${column.column_comment}', 'SCHEMA', 'dbo', 'TABLE', '${column.table}', 'COLUMN', '${column.column_name}'; \nGO\n`);
      }
    });
  });

  return ddls;
};

module.exports = MSSQLHelper;
